package ch.fhnw.kosmonauten.server.dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ch.fhnw.kosmonauten.model.Dossier;
import ch.fhnw.kosmonauten.model.User;

/**
 * Diese Klasse erstellt einen Request.
 * 
 * @author Fabian Siegrist
 * @author Oliver Freiermuth
 * @author Mikhail Onoufrienko
 * @author Risto Milani
 * @version $Revision: 220 $
 *          File name: $HeadURL: http://iching-kosmonauten.googlecode.com/svn/I-Ching/src/ch/fhnw/kosmonauten/server/dal/DossierAccessLayer.java $
 *          Last modified:$Date: 2013-05-27 19:09:32 +0000 (Mon, 27 May 2013) $
 *          Last modified by: $Author: molotok.mihon@gmail.com $
 */

// class definition
public class DossierAccessLayer {

	// attributes
	private Statement s;
	private ResultSet rs;
	private Connection con;
	private Dossier dossier;

	// methods
	/**
	 * @return
	 * @throws SQLException 
	 */
	public Dossier[] getDossier() throws SQLException {

		ArrayList<Dossier> response = new ArrayList<Dossier>();

		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			rs = s.executeQuery("select * from dossier;");
			while (rs.next()) {
				response.add(new Dossier(Integer.parseInt(rs.getString(1)), rs
						.getString(2), Integer.parseInt(rs.getString(3)),
						Integer.parseInt(rs.getString(4)), rs.getString(5), rs
								.getString(6), rs.getString(7),
						rs.getString(8), rs.getString(9), rs.getString(10),
						Integer.parseInt(rs.getString(11))));
			}
			return response.toArray(new Dossier[0]);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		} finally {
			rs.close();
			s.close();
			con.close();
		}
	}
	
	public Dossier createDossier(Dossier dossier) throws SQLException {

		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			System.out.println("SQL >>> INSERT INTO dossier"+ 
					" (Dossiername, UserID, CategoryID, Question, Analyse, Synthese, Experience, Hexagramcode, Binarycode, Privacy, EditDate, CreateDate, "+ 
					" Situation, Perspective, Nuclear, Inverse, Reverse, Fuxi, "+ 
					" Sit_line1, Sit_line2, Sit_line3, Sit_line4, Sit_line5, Sit_line6, "+
					" Per_line1, Per_line2, Per_line3, Per_line4, Per_line5, Per_line6, "+
					" Nuc_line1, Nuc_line2, Nuc_line3, Nuc_line4, Nuc_line5, Nuc_line6, "+
					" Inv_line1, Inv_line2, Inv_line3, Inv_line4, Inv_line5, Inv_line6, "+ 
					" Rev_line1, Rev_line2, Rev_line3, Rev_line4, Rev_line5, Rev_line6, "+
					" Fux_line1, Fux_line2, Fux_line3, Fux_line4, Fux_line5, Fux_line6 ) " +
					"VALUES ('"+dossier.getDossierName()+"', "+dossier.getUserID()+", "+dossier.getCategoryID()+", '"+dossier.getQuestion()+"', '"+dossier.getAnalyse()+"', '"+dossier.getSynthese()+"', '"+dossier.getExperience()+"', '"+dossier.getHexagramcode()+"', '"+dossier.getBinarycode()+"', "+Integer.toString(dossier.getPrivacy())+", '"+dossier.getCreateDate()+"', '"+dossier.getCreateDate()+"'," +
					" '"+dossier.getSituation()+"', '"+dossier.getPerspective()+"', '"+dossier.getNuclear()+"', '"+dossier.getInverse()+"', '"+dossier.getReverse()+"', '"+dossier.getFuxi()+"',"+
					" '"+dossier.getSit_line1()+"', '"+dossier.getSit_line2()+"', '"+dossier.getSit_line3()+"', '"+dossier.getSit_line4()+"', '"+dossier.getSit_line5()+"', '"+dossier.getSit_line6()+"', " +
					" '"+dossier.getPer_line1()+"', '"+dossier.getPer_line2()+"', '"+dossier.getPer_line3()+"', '"+dossier.getPer_line4()+"', '"+dossier.getPer_line5()+"', '"+dossier.getPer_line6()+"', " +
					" '"+dossier.getNuc_line1()+"', '"+dossier.getNuc_line2()+"', '"+dossier.getNuc_line3()+"', '"+dossier.getNuc_line4()+"', '"+dossier.getNuc_line5()+"', '"+dossier.getNuc_line6()+"', " +
					" '"+dossier.getInv_line1()+"', '"+dossier.getInv_line2()+"', '"+dossier.getInv_line3()+"', '"+dossier.getInv_line4()+"', '"+dossier.getInv_line5()+"', '"+dossier.getInv_line6()+"', " +
					" '"+dossier.getRev_line1()+"', '"+dossier.getRev_line2()+"', '"+dossier.getRev_line3()+"', '"+dossier.getRev_line4()+"', '"+dossier.getRev_line5()+"', '"+dossier.getRev_line6()+"', " +
					" '"+dossier.getFux_line1()+"', '"+dossier.getFux_line2()+"', '"+dossier.getFux_line3()+"', '"+dossier.getFux_line4()+"', '"+dossier.getFux_line5()+"', '"+dossier.getFux_line6()+"');");
			s.executeUpdate("INSERT INTO dossier"+ 
						" (Dossiername, UserID, CategoryID, Question, Analyse, Synthese, Experience, Hexagramcode, Binarycode, Privacy, EditDate, CreateDate, "+ 
						" Situation, Perspective, Nuclear, Inverse, Reverse, Fuxi, "+ 
						" Sit_line1, Sit_line2, Sit_line3, Sit_line4, Sit_line5, Sit_line6, "+
						" Per_line1, Per_line2, Per_line3, Per_line4, Per_line5, Per_line6, "+
						" Nuc_line1, Nuc_line2, Nuc_line3, Nuc_line4, Nuc_line5, Nuc_line6, "+
						" Inv_line1, Inv_line2, Inv_line3, Inv_line4, Inv_line5, Inv_line6, "+ 
						" Rev_line1, Rev_line2, Rev_line3, Rev_line4, Rev_line5, Rev_line6, "+
						" Fux_line1, Fux_line2, Fux_line3, Fux_line4, Fux_line5, Fux_line6 ) " +
						"VALUES ('"+dossier.getDossierName()+"', "+dossier.getUserID()+", "+dossier.getCategoryID()+", '"+dossier.getQuestion()+"', '"+dossier.getAnalyse()+"', '"+dossier.getSynthese()+"', '"+dossier.getExperience()+"', '"+dossier.getHexagramcode()+"', '"+dossier.getBinarycode()+"', "+Integer.toString(dossier.getPrivacy())+", '"+dossier.getCreateDate()+"', '"+dossier.getCreateDate()+"'," +
						" '"+dossier.getSituation()+"', '"+dossier.getPerspective()+"', '"+dossier.getNuclear()+"', '"+dossier.getInverse()+"', '"+dossier.getReverse()+"', '"+dossier.getFuxi()+"',"+
						" '"+dossier.getSit_line1()+"', '"+dossier.getSit_line2()+"', '"+dossier.getSit_line3()+"', '"+dossier.getSit_line4()+"', '"+dossier.getSit_line5()+"', '"+dossier.getSit_line6()+"', " +
						" '"+dossier.getPer_line1()+"', '"+dossier.getPer_line2()+"', '"+dossier.getPer_line3()+"', '"+dossier.getPer_line4()+"', '"+dossier.getPer_line5()+"', '"+dossier.getPer_line6()+"', " +
						" '"+dossier.getNuc_line1()+"', '"+dossier.getNuc_line2()+"', '"+dossier.getNuc_line3()+"', '"+dossier.getNuc_line4()+"', '"+dossier.getNuc_line5()+"', '"+dossier.getNuc_line6()+"', " +
						" '"+dossier.getInv_line1()+"', '"+dossier.getInv_line2()+"', '"+dossier.getInv_line3()+"', '"+dossier.getInv_line4()+"', '"+dossier.getInv_line5()+"', '"+dossier.getInv_line6()+"', " +
						" '"+dossier.getRev_line1()+"', '"+dossier.getRev_line2()+"', '"+dossier.getRev_line3()+"', '"+dossier.getRev_line4()+"', '"+dossier.getRev_line5()+"', '"+dossier.getRev_line6()+"', " +
						" '"+dossier.getFux_line1()+"', '"+dossier.getFux_line2()+"', '"+dossier.getFux_line3()+"', '"+dossier.getFux_line4()+"', '"+dossier.getFux_line5()+"', '"+dossier.getFux_line6()+"');");
			System.out.println("SQL >>> "+"SELECT MAX(DossierID) DossierID FROM dossier");
			rs = s.executeQuery("SELECT MAX(DossierID) DossierID FROM dossier;");
			if(rs.next())
			{
				dossier.setDossierID(Integer.parseInt(rs.getString(1)));
			}
			rs.close();
			return dossier;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return dossier;
		} finally {
			
			s.close();
			con.close();
		}
	}
	
	public boolean saveDossier(Dossier dossier) throws SQLException {

		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			System.out.println("SQL >>> UPDATE dossier"+ 
					" SET Dossiername = '" + dossier.getDossierName()+ "',"+ 
					" Question = '" +dossier.getQuestion() + "',"+ 
					" Analyse = '" + dossier.getAnalyse()+ "',"+ 
					" Synthese = '" +dossier.getSynthese() + "',"+ 
					" Experience = '" +dossier.getExperience() + "',"+ 
					" Privacy = " + dossier.getPrivacy() + ", "+
					" EditDate = NOW(),"+ 
					" Situation  = '" + dossier.getSituation() + "',"+ 
					" Perspective = '" + dossier.getPerspective() + "', "+
					" Nuclear = '" + dossier.getNuclear()+ "', "+
					" Inverse = '" + dossier.getInverse()+ "', "+
					" Reverse = '" + dossier.getReverse()+ "', "+
					" Fuxi = '" + dossier.getFuxi()+ "',  "+
					" Sit_line1 = '" + dossier.getSit_line1() + "',"+ 
					" Sit_line2 = '" + dossier.getSit_line2()+ "', "+
					" Sit_line3 = '" + dossier.getSit_line3()+ "', "+
					" Sit_line4 = '" + dossier.getSit_line4()+ "', "+
					" Sit_line5 = '" + dossier.getSit_line5()+ "', "+
					" Sit_line6 = '" + dossier.getSit_line6()+ "', "+
					" Per_line1 = '" + dossier.getPer_line1()+ "', "+
					" Per_line2 = '" + dossier.getPer_line2()+ "', "+
					" Per_line3 = '" + dossier.getPer_line3()+ "', "+
					" Per_line4 = '" + dossier.getPer_line4()+ "', "+
					" Per_line5 = '" + dossier.getPer_line5()+ "', "+
					" Per_line6 = '" + dossier.getPer_line6()+ "', "+
					" Nuc_line1 = '" + dossier.getNuc_line1()+ "', "+
					" Nuc_line2 = '" + dossier.getNuc_line2()+ "', "+
					" Nuc_line3 = '" + dossier.getNuc_line3()+ "', "+
					" Nuc_line4 = '" + dossier.getNuc_line4()+ "', "+
					" Nuc_line5 = '" + dossier.getNuc_line5()+ "', "+
					" Nuc_line6 = '" + dossier.getNuc_line6()+ "', "+
					" Inv_line1 = '" + dossier.getInv_line1()+ "', "+
					" Inv_line2 = '" + dossier.getInv_line2()+ "', "+
					" Inv_line3 = '" + dossier.getInv_line3()+ "', "+
					" Inv_line4 = '" + dossier.getInv_line4()+ "', "+
					" Inv_line5 = '" + dossier.getInv_line5()+ "', "+
					" Inv_line6 = '" + dossier.getInv_line6()+ "', "+
					" Rev_line1 = '" + dossier.getRev_line1()+ "', "+
					" Rev_line2 = '" + dossier.getRev_line2()+ "', "+
					" Rev_line3 = '" + dossier.getRev_line3()+ "', "+
					" Rev_line4 = '" + dossier.getRev_line4()+ "', "+
					" Rev_line5 = '" + dossier.getRev_line5()+ "', "+
					" Rev_line6 = '" + dossier.getRev_line6()+ "', "+
					" Fux_line1 = '" + dossier.getFux_line1()+ "', "+
					" Fux_line2 = '" + dossier.getFux_line2()+ "', "+
					" Fux_line3 = '" + dossier.getFux_line3()+ "', "+
					" Fux_line4 = '" + dossier.getFux_line4()+ "', "+
					" Fux_line5 = '" + dossier.getFux_line5()+ "', "+
					" Fux_line6 = '" + dossier.getFux_line6()+ "' " +
					" WHERE DossierID = " + dossier.getDossierID()+ ";");
			s.executeUpdate("UPDATE dossier"+ 
					" SET Dossiername = '" + dossier.getDossierName()+ "',"+ 
					" Question = '" +dossier.getQuestion() + "',"+ 
					" Analyse = '" + dossier.getAnalyse()+ "',"+ 
					" Synthese = '" +dossier.getSynthese() + "',"+ 
					" Experience = '" +dossier.getExperience() + "',"+ 
					" Privacy = " + dossier.getPrivacy() + ", "+
					" EditDate = NOW(),"+ 
					" Situation  = '" + dossier.getSituation() + "',"+ 
					" Perspective = '" + dossier.getPerspective() + "', "+
					" Nuclear = '" + dossier.getNuclear()+ "', "+
					" Inverse = '" + dossier.getInverse()+ "', "+
					" Reverse = '" + dossier.getReverse()+ "', "+
					" Fuxi = '" + dossier.getFuxi()+ "',  "+
					" Sit_line1 = '" + dossier.getSit_line1() + "',"+ 
					" Sit_line2 = '" + dossier.getSit_line2()+ "', "+
					" Sit_line3 = '" + dossier.getSit_line3()+ "', "+
					" Sit_line4 = '" + dossier.getSit_line4()+ "', "+
					" Sit_line5 = '" + dossier.getSit_line5()+ "', "+
					" Sit_line6 = '" + dossier.getSit_line6()+ "', "+
					" Per_line1 = '" + dossier.getPer_line1()+ "', "+
					" Per_line2 = '" + dossier.getPer_line2()+ "', "+
					" Per_line3 = '" + dossier.getPer_line3()+ "', "+
					" Per_line4 = '" + dossier.getPer_line4()+ "', "+
					" Per_line5 = '" + dossier.getPer_line5()+ "', "+
					" Per_line6 = '" + dossier.getPer_line6()+ "', "+
					" Nuc_line1 = '" + dossier.getNuc_line1()+ "', "+
					" Nuc_line2 = '" + dossier.getNuc_line2()+ "', "+
					" Nuc_line3 = '" + dossier.getNuc_line3()+ "', "+
					" Nuc_line4 = '" + dossier.getNuc_line4()+ "', "+
					" Nuc_line5 = '" + dossier.getNuc_line5()+ "', "+
					" Nuc_line6 = '" + dossier.getNuc_line6()+ "', "+
					" Inv_line1 = '" + dossier.getInv_line1()+ "', "+
					" Inv_line2 = '" + dossier.getInv_line2()+ "', "+
					" Inv_line3 = '" + dossier.getInv_line3()+ "', "+
					" Inv_line4 = '" + dossier.getInv_line4()+ "', "+
					" Inv_line5 = '" + dossier.getInv_line5()+ "', "+
					" Inv_line6 = '" + dossier.getInv_line6()+ "', "+
					" Rev_line1 = '" + dossier.getRev_line1()+ "', "+
					" Rev_line2 = '" + dossier.getRev_line2()+ "', "+
					" Rev_line3 = '" + dossier.getRev_line3()+ "', "+
					" Rev_line4 = '" + dossier.getRev_line4()+ "', "+
					" Rev_line5 = '" + dossier.getRev_line5()+ "', "+
					" Rev_line6 = '" + dossier.getRev_line6()+ "', "+
					" Fux_line1 = '" + dossier.getFux_line1()+ "', "+
					" Fux_line2 = '" + dossier.getFux_line2()+ "', "+
					" Fux_line3 = '" + dossier.getFux_line3()+ "', "+
					" Fux_line4 = '" + dossier.getFux_line4()+ "', "+
					" Fux_line5 = '" + dossier.getFux_line5()+ "', "+
					" Fux_line6 = '" + dossier.getFux_line6()+ "' " +
					" WHERE DossierID = " + dossier.getDossierID()+ ";");
			
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		} finally {
			s.close();
			con.close();
		}
	}
	
	public Dossier[] getDossierLoad(User user) throws SQLException {

		ArrayList<Dossier> response = new ArrayList<Dossier>();
		
		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			rs = s.executeQuery("SELECT d.*, c.Categoryname, u.Name, s.Languagename, st.Hexagramname " +
								"FROM dossier d " +
								"INNER JOIN category c ON c.CategoryID = d.CategoryID " +
								"INNER JOIN user u ON u.UserID = d.UserID " +
								"INNER JOIN systemLanguage s ON s.LanguageID = u.LanguageID " +
								"INNER JOIN hexagrammapping h ON h.Binarycode = d.Binarycode " +
								"INNER JOIN systemTranslation st ON st.HexagramID = h.HexagramID AND st.LanguageID = s.LanguageID " +
								"WHERE d.UserID = " + user.getId() + " OR d.Privacy = 0 " +
								"ORDER BY c.CategoryID, d.Dossiername;");
			while (rs.next()) {
				dossier = new Dossier();
				dossier.setDossierID(Integer.parseInt(rs.getString(1)));
				dossier.setDossierName(rs.getString(2));
				dossier.setUserID(Integer.parseInt(rs.getString(3)));
				dossier.setCategoryID(Integer.parseInt(rs.getString(4)));
				dossier.setQuestion(rs.getString(5));
				dossier.setAnalyse(rs.getString(6));
				dossier.setSynthese(rs.getString(7));
				dossier.setExperience(rs.getString(8));
				dossier.setHexagramcode(rs.getString(9));
				dossier.setBinarycode(rs.getString(10));
				//dossier.setBinarycodePerspective(rs.getString(1));
				dossier.setPrivacy(Integer.parseInt(rs.getString(11)));
				dossier.setCreateDate(rs.getString(12));
				dossier.setEditDate(rs.getString(13));
				dossier.setSituation(rs.getString(14));
				dossier.setPerspective(rs.getString(15));
				dossier.setNuclear(rs.getString(16));
				dossier.setInverse(rs.getString(17));
				dossier.setReverse(rs.getString(18));
				dossier.setFuxi(rs.getString(19));
				dossier.setSit_line1(rs.getString(20));
				dossier.setSit_line2(rs.getString(21));
				dossier.setSit_line3(rs.getString(22));
				dossier.setSit_line4(rs.getString(23));
				dossier.setSit_line5(rs.getString(24));
				dossier.setSit_line6(rs.getString(25));
				dossier.setPer_line1(rs.getString(26));
				dossier.setPer_line2(rs.getString(27));
				dossier.setPer_line3(rs.getString(28));
				dossier.setPer_line4(rs.getString(29));
				dossier.setPer_line5(rs.getString(30));
				dossier.setPer_line6(rs.getString(31));
				dossier.setNuc_line1(rs.getString(32));
				dossier.setNuc_line2(rs.getString(33));
				dossier.setNuc_line3(rs.getString(34));
				dossier.setNuc_line4(rs.getString(35));
				dossier.setNuc_line5(rs.getString(36));
				dossier.setNuc_line6(rs.getString(37));
				dossier.setInv_line1(rs.getString(38));
				dossier.setInv_line2(rs.getString(39));
				dossier.setInv_line3(rs.getString(40));
				dossier.setInv_line4(rs.getString(41));
				dossier.setInv_line5(rs.getString(42));
				dossier.setInv_line6(rs.getString(43));
				dossier.setRev_line1(rs.getString(44));
				dossier.setRev_line2(rs.getString(45));
				dossier.setRev_line3(rs.getString(46));
				dossier.setRev_line4(rs.getString(47));
				dossier.setRev_line5(rs.getString(48));
				dossier.setRev_line6(rs.getString(49));
				dossier.setFux_line1(rs.getString(50));
				dossier.setFux_line2(rs.getString(51));
				dossier.setFux_line3(rs.getString(52));
				dossier.setFux_line4(rs.getString(53));
				dossier.setFux_line5(rs.getString(54));
				dossier.setFux_line6(rs.getString(55));
				dossier.setCategoryname(rs.getString(56));
				dossier.setUsername(rs.getString(57));
				dossier.setLanguagename(rs.getString(58));
				dossier.setHexagramname(rs.getString(59));
				response.add(dossier);
			}
			return response.toArray(new Dossier[0]);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		} finally {
			rs.close();
			s.close();
			con.close();
		}
	}
	// GET DOSSIER LISTE
	/*
SELECT c.Categoryname, u.Name, s.Languagename,st.Hexagramname
FROM dossier d
INNER JOIN category c ON c.CategoryID = d.CategoryID
INNER JOIN user u ON u.UserID = d.UserID
INNER JOIN systemLanguage s ON s.LanguageID = u.LanguageID
INNER JOIN hexagrammapping h ON h.Binarycode = d.Binarycode
INNER JOIN systemTranslation st ON st.HexagramID = h.HexagramID AND st.LanguageID = s.LanguageID
WHERE d.UserID = 234567 OR d.Privacy = 0
ORDER BY c.CategoryID, d.Dossiername
	*/

}
